# Using lookup tables

Lookup tables are useful for replacing source data with the actual data that you want to appear in the Storage Zone. For example, a lookup table could be used to replace a zip code with a full address or, conversely, to replace a full address with a zip code.

Information note

Lookup on a column which is mapped to the Compose "From\_\_Date" column is not supported.

## Linking lookup tables

To link a lookup table column to a Storage Zone table column:

1. Click the link to the desired task in the **Storage Zone** panel.

   The **Manage Tasks** window opens.

2. In the **Mappings** column, click the mapping for the Storage Zone table containing the result column (with the data that you want to replace).

   The **Edit Mapping -**  **Name** window opens.

3. Hover the mouse cursor over the relevant Storage Zone column and then click the **Lookup** button that appears to the right of the column name.

   The **Select Lookup Table** window opens.

   1. From the **Database** drop-down list, select the database containing the lookup table.

      Information note

      The database must reside in your Landing Zone.

   2. From the **Schema** drop-down list, select the schema containing your source lookup tables.

   3. Select either **Table** or **View** according to the lookup table type.

   4. From the **Table** drop-down list, select the lookup table.

      The right side of the **Select Lookup Table** window displays the lookup table columns and their data types. To view the data in the lookup table, click the **Show Lookup Data** button.

   5. After you have selected the lookup table, click **OK**.

   The **Lookup Transformations** - **Table Name**.**Column Name** window opens.

   The window is divided into the following panes:

   **Upper pane:** The upper part of the right pane (**Condition**) displays the condition expression, which stipulates the condition(s) for performing the lookup.

   **Lower pane:** The lower part of the right pane (**Result Column**) displays the column result expression, which stipulates what data to replace in the target column.

4. To change the lookup table, click the **Change Lookup Table** button above the lookup table columns and then perform steps a. to d. above.

5. To view the lookup table or landing table data, click the **Show Lookup Data** or **Show Landing Data** buttons respectively.

6. To specify the condition(s) for performing the lookup, click the **Create Expression** button (which changes to **Edit Expression** after an expression has been created) above the **Condition** expression.

   The **Condition Expression -**  **Column Name** window opens.

7. Create an expression using the landing and lookup table columns on the left.

   For an example, see [Using lookup tables](https://help.qlik.com/en-US/compose/December2024/Content/ComposeDWDL/Main/DL/Storage%20Zone/use_lookup_tables_hive.htm#).

   For information on creating expressions, see [Creating transformations](https://help.qlik.com/en-US/compose/December2024/Content/ComposeDWDL/Main/DL/Model/create_expressions.htm).

8. To specify what data to replace or add if the lookup conditions are met, click the **Create Expression** button (which changes to **Edit Expression** after an expression has been created) above the **Result** **Column** expression.

   The **Result Expression -**  **Column Name** window opens.

9. Create an expression using the landing and lookup table columns on the left.

   For an example, see [Using lookup tables](https://help.qlik.com/en-US/compose/December2024/Content/ComposeDWDL/Main/DL/Storage%20Zone/use_lookup_tables_hive.htm#).

   For information on creating expressions, see [Creating transformations](https://help.qlik.com/en-US/compose/December2024/Content/ComposeDWDL/Main/DL/Model/create_expressions.htm).

10. To preview the results, click the **Preview Results** button.

11. Click **OK** to save your settings and close the **Lookup Transformations** - **Table Name**.**Column Name** window.

## Lookup example

The following example shows how a lookup table is used to concatenate a Dutch translation of the category name (located in the lookup table) to the original category name located in the landing table.

The lookup could be defined using the following expressions:

1. Condition expression:

   `${Lookup.CategoryID}=${Landing.CategoryID}`

   **Meaning:** Perform the lookup only if the Category ID in the landing table and the lookup table are the same.

2. Result column expression:

   `${Lookup.CategoryName} + ' is ' + ${Landing.CategoryName}`

   **Meaning:** Add the data in the **CategoryName** column in the lookup table to the data in the **CategoryName** column in the landing table (separated by the word "is").

Assuming the result column name is "Split Name", clicking the Preview Results button would display the following table:

| **Split Name** | **Category Name (Lookup)** | **Category Name (Landing)** | **Category ID (Lookup)** | **Category ID (Landing)** |
| --- | --- | --- | --- | --- |
| dranken is Beverages | dranken | Beverages | 1 | 1 |
| Specerijen is Condiments | Specerijen | Condiments | 2 | 2 |
| Gebak is Confectionary | Gebak | Confectionary | 3 | 3 |
| Zuivelproducten is Dairy Products | Zuivelproducten | Dairy Products | 4 | 4 |
| Grains/Granen is Grains/Cereal | Grains/Granen | Grains/Cereal | 5 | 5 |
| Vlees/Gevolgete is Meat/Poultry | Vlees/Gevolgete | Meat/Poultry | 6 | 6 |